/**
 * www.easyplatform.cn ©2016
 */
package cn.easyplatform.studio.web.editors.workbench;

import cn.easyplatform.entities.beans.project.ProjectBean;
import cn.easyplatform.entities.beans.table.TableBean;
import cn.easyplatform.entities.beans.table.TableField;
import cn.easyplatform.lang.Strings;
import cn.easyplatform.studio.StudioApp;
import cn.easyplatform.studio.cmd.entity.GetEntityCmd;
import cn.easyplatform.studio.cmd.entity.QueryModelCmd;
import cn.easyplatform.studio.cmd.entity.QueryTableCmd;
import cn.easyplatform.studio.context.Contexts;
import cn.easyplatform.studio.dao.DaoUtils;
import cn.easyplatform.studio.utils.WebUtils;
import cn.easyplatform.studio.vos.*;
import cn.easyplatform.studio.web.editors.AbstractPanelEditor;
import cn.easyplatform.studio.web.editors.Editor;
import cn.easyplatform.studio.web.editors.EditorCallback;
import cn.easyplatform.studio.web.layout.WorkbenchController;
import cn.easyplatform.studio.web.views.impl.AbstractView;
import cn.easyplatform.type.EntityType;
import cn.easyplatform.web.ext.cmez.CMeditor;
import org.apache.commons.lang3.StringUtils;
import org.zkoss.util.resource.Labels;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.Page;
import org.zkoss.zk.ui.event.Event;
import org.zkoss.zk.ui.event.EventListener;
import org.zkoss.zk.ui.event.Events;
import org.zkoss.zk.ui.event.OpenEvent;
import org.zkoss.zul.*;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author <a href="mailto:shiny_vc@163.com">陈云亮</a> <br/>
 * @since 2.0.0 <br/>
 */
public class SqlWizardEditor extends AbstractPanelEditor {

    private Listbox tables;

    private CMeditor editor;

    private Grid tableGrid;

    private Grid conditionGrid;

    private SqlWizardVo sqlWizardVo = new SqlWizardVo();

    /*private Textbox prefix;

    private Button select;

    private Button insert;

    private Button update;

    private Button delete;

    private Button reset;

    private Component container;*/

    private Map<EntityVo, List<EntityVo>> data;

    /**
     * @param workbench
     * @param id
     */
    public SqlWizardEditor(WorkbenchController workbench, String id) {
        super(workbench, id);
    }

    @Override
    public void create(Object... args) {
        Idspace is = createPanel(Labels.getLabel("menu.sql.wizard"), "~./images/sql.gif", "~./include/editor/workbench/sqlwizard.zul");
        tables = (Listbox) is.getFellow("sqlWizardEditor_listbox_tables");
        editor = (CMeditor) is.getFellow("sqlWizardEditor_cmeditor_select_SQL");
        editor.setTheme(Contexts.getUser().getEditorTheme());
        tableGrid = (Grid) is.getFellow("sqlWizardEditor_grid_select_table");
        conditionGrid = (Grid) is.getFellow("sqlWizardEditor_grid_select_condition");
        data = new HashMap<EntityVo, List<EntityVo>>();
        redraw();
        createConditionGrid();
    }

    private void redraw() {
        List<EntityVo> ds = StudioApp.execute(new QueryModelCmd(
                EntityType.DATASOURCE.getName()));
        ProjectBean pb = Contexts.getProject();
        EntityVo master = null;
        for (EntityVo vo : ds) {
            if (pb.getBizDb().equals(vo.getId())) {
                master = vo;
                break;
            }
        }
        ds.remove(master);
        Listgroup group = createDb(master);
        List<EntityVo> result = StudioApp.execute(new QueryTableCmd(master
                .getId()));
        createTables(group, result);
        data.put(master, result);

        for (EntityVo vo : ds) {
            group = createDb(vo);
            group.setOpen(false);
            group.addEventListener(Events.ON_OPEN, this);
        }
        tables.addEventListener(Events.ON_SELECT, this);
        tables.getPaginal().setDetailed(false);
    }

    private Listgroup createDb(EntityVo ds) {
        Listgroup group = new Listgroup();
        group.appendChild(new Listcell(ds.getId()));
        group.appendChild(new Listcell(ds.getName()));
        group.setValue(ds);
        tables.appendChild(group);
        return group;
    }

    private void createTables(Listgroup group, List<EntityVo> data) {
        List<Listitem> items = tables.getItems();
        int pos = items.indexOf(group);
        for (int i = 0; i < data.size(); i++) {
            EntityVo tv = data.get(i);
            Listitem ti = new Listitem();
            ti.appendChild(new Listcell(tv.getId()));
            ti.appendChild(new Listcell(tv.getName()));
            ti.setDraggable("table");
            ti.setValue(tv);
            ti.addEventListener(Events.ON_DOUBLE_CLICK, this);
            items.add(pos + i + 1, ti);
        }
    }

    @Override
    public void dispatch(Event event) {
        Component c = event.getTarget();
        if (c instanceof Listgroup) {
            OpenEvent evt = (OpenEvent) event;
            if (evt.isOpen() && evt.getTarget().getAttribute("loaded") == null) {
                Listgroup group = (Listgroup) c;
                EntityVo ds = group.getValue();
                List<EntityVo> result = StudioApp.execute(new QueryTableCmd(ds
                        .getId()));
                createTables((Listgroup) c, result);
                data.put(ds, result);
                evt.getTarget().setAttribute("loaded", "1");
            }
        } else if (c == tables) {
            EntityVo vo = tables.getSelectedItem().getValue();
            TableBean tb = (TableBean) StudioApp.execute(new GetEntityCmd(vo
                    .getId()));
            if (tb != null) {
                Boolean canAdd = true;
                for (int index = 0; index < sqlWizardVo.getSqlTableBeanList().size(); index++) {
                    if (sqlWizardVo.getSqlTableBeanList().get(index).getId().equals(tb.getId())) {
                        canAdd = false;
                        break;
                    }
                }

                if (canAdd == true)
                    sqlWizardVo.getSqlTableBeanList().add(tb);
            }
            createTableGrid();
            createConditionGrid();
        } else if (c instanceof Window && event.getName().equals(Events.ON_CLOSE)) {
            String tableID = c.getId();
            int deleteIndex = -1;
            for (int index = 0; index < sqlWizardVo.getSqlTableBeanList().size(); index++) {
                if (sqlWizardVo.getSqlTableBeanList().get(index).getId().equals(tableID))
                    deleteIndex = index;
            }

            if (deleteIndex >= 0)
                sqlWizardVo.getSqlTableBeanList().remove(deleteIndex);
            createTableGrid();
        } else if (c.getId().equals("sqlWizardEditor_grid_select_condition_clear")) {
            sqlWizardVo.setSqlFieldVoList(new ArrayList<SqlFieldVo>());
            sqlWizardVo.setSqlConditionVoList(new ArrayList<SqlConditionVo>());
            sqlWizardVo.setSqlOrderVoList(new ArrayList<SqlOrderVo>());
            createConditionGrid();
        } else if (c.getId().equals("sqlWizardEditor_grid_select_condition_execute")) {
            Boolean isError = false;
            for (SqlConditionVo sqlConditionVo : sqlWizardVo.getSqlConditionVoList()) {
                if (sqlConditionVo.getConditionType().equals(SqlConditionVo.ConditionType.IN.getName()) ||
                        sqlConditionVo.getConditionType().equals(SqlConditionVo.ConditionType.NOIN.getName())) {
                    Pattern pattern = Pattern.compile("^(\\'[^\\',]+\\')(,\\'[^\\',]+\\')*$", Pattern.CASE_INSENSITIVE);
                    Matcher matcher = pattern.matcher(sqlConditionVo.getValueContent());
                    if (matcher.find() == false) {
                        isError = true;
                        break;
                    }
                }
            }

            if (isError == true) {
                WebUtils.showError(Labels.getLabel("sqlwizard.condition.in.placeholder"));
            } else {
                String sqlString = getSqlStringWithSqlWizardVo();
                editor.setValue(sqlString);
            }
        } else if (c instanceof Bandbox) {
            Bandbox search = (Bandbox) c;
            String val = null;
            if (event instanceof OpenEvent) {
                OpenEvent evt = (OpenEvent) event;
                val = (String) evt.getValue();
            } else
                val = search.getValue();
            search(val);
        } else if (c.getId().equals("sqlWizardEditor_grid_select_condition_save")) {
            // TODO: 2019-11-12 保存记录
        }
    }

    private void search(String val) {
        if (Strings.isBlank(val)) {
            for (Listgroup group : tables.getGroups()) {
                List<EntityVo> cv = data.get(group.getValue());
                if (cv != null) {
                    tables.getItems().removeAll(group.getItems());
                    createTables(group, cv);
                }
            }
        } else {
            for (Listgroup group : tables.getGroups()) {
                List<EntityVo> cv = data.get(group.getValue());
                if (cv != null) {
                    tables.getItems().removeAll(group.getItems());
                    List<EntityVo> cp = new ArrayList<EntityVo>();
                    for (EntityVo tv : cv) {
                        if (StringUtils.indexOfIgnoreCase(tv.getId(), val) >= 0
                                || StringUtils.indexOfIgnoreCase(tv.getName(),
                                val) >= 0) {
                            cp.add(tv);
                        }
                    }
                    createTables(group, cp);
                }
            }
        }
    }
    //data
    private String getSringWithConditiontype(String conditiontype, SqlConditionVo.ConditionType type) {
        String typeStr = null;
        if (SqlConditionVo.ConditionType.EQUAL.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.EQUAL) {
            typeStr = Labels.getLabel("sqlwizard.condition.equal");
        } else if (SqlConditionVo.ConditionType.NOEQUAL.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.NOEQUAL) {
            typeStr = Labels.getLabel("sqlwizard.condition.noEqual");
        } else if (SqlConditionVo.ConditionType.LESS.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.LESS) {
            typeStr = Labels.getLabel("sqlwizard.condition.less");
        } else if (SqlConditionVo.ConditionType.LESSOREQUAL.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.LESSOREQUAL) {
            typeStr = Labels.getLabel("sqlwizard.condition.lessOrEqual");
        } else if (SqlConditionVo.ConditionType.MORE.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.MORE) {
            typeStr = Labels.getLabel("sqlwizard.condition.more");
        } else if (SqlConditionVo.ConditionType.MOREOREQUAL.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.MOREOREQUAL) {
            typeStr = Labels.getLabel("sqlwizard.condition.moreOrEqual");
        } else if (SqlConditionVo.ConditionType.CONTAIN.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.CONTAIN) {
            typeStr = Labels.getLabel("sqlwizard.condition.contain");
        } else if (SqlConditionVo.ConditionType.NOCONTAIN.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.NOCONTAIN) {
            typeStr = Labels.getLabel("sqlwizard.condition.noContain");
        } else if (SqlConditionVo.ConditionType.START.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.START) {
            typeStr = Labels.getLabel("sqlwizard.condition.start");
        } else if (SqlConditionVo.ConditionType.NOSTART.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.NOSTART) {
            typeStr = Labels.getLabel("sqlwizard.condition.noStart");
        } else if (SqlConditionVo.ConditionType.END.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.END) {
            typeStr = Labels.getLabel("sqlwizard.condition.end");
        } else if (SqlConditionVo.ConditionType.NOEND.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.NOEND) {
            typeStr = Labels.getLabel("sqlwizard.condition.noEnd");
        } else if (SqlConditionVo.ConditionType.NULL.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.NULL) {
            typeStr = Labels.getLabel("sqlwizard.condition.null");
        } else if (SqlConditionVo.ConditionType.NONULL.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.NONULL) {
            typeStr = Labels.getLabel("sqlwizard.condition.noNull");
        } else if (SqlConditionVo.ConditionType.EMPTY.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.EMPTY) {
            typeStr = Labels.getLabel("sqlwizard.condition.empty");
        } else if (SqlConditionVo.ConditionType.NOEMPTY.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.NOEMPTY) {
            typeStr = Labels.getLabel("sqlwizard.condition.noEmpty");
        } else if (SqlConditionVo.ConditionType.BETWEEN.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.BETWEEN) {
            typeStr = Labels.getLabel("sqlwizard.condition.between");
        } else if (SqlConditionVo.ConditionType.NOBETWEEN.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.NOBETWEEN) {
            typeStr = Labels.getLabel("sqlwizard.condition.noBetween");
        } else if (SqlConditionVo.ConditionType.IN.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.IN) {
            typeStr = Labels.getLabel("sqlwizard.condition.in");
        } else if (SqlConditionVo.ConditionType.NOIN.getName().equals(conditiontype) ||
                type == SqlConditionVo.ConditionType.NOIN) {
            typeStr = Labels.getLabel("sqlwizard.condition.noIn");
        }
        return typeStr;
    }

    private String getSringWithStatType(String conditiontype, SqlFieldVo.StatType type) {
        String typeStr = null;
        if (SqlFieldVo.StatType.COUNT.getName().equals(conditiontype) ||
                type == SqlFieldVo.StatType.COUNT) {
            typeStr = Labels.getLabel("sqlwizard.field.count");
        } else if (SqlFieldVo.StatType.SUM.getName().equals(conditiontype) ||
                type == SqlFieldVo.StatType.SUM) {
            typeStr = Labels.getLabel("sqlwizard.field.sum");
        } else if (SqlFieldVo.StatType.AVG.getName().equals(conditiontype) ||
                type == SqlFieldVo.StatType.AVG) {
            typeStr = Labels.getLabel("sqlwizard.field.avg");
        } else if (SqlFieldVo.StatType.MAX.getName().equals(conditiontype) ||
                type == SqlFieldVo.StatType.MAX) {
            typeStr = Labels.getLabel("sqlwizard.field.max");
        } else if (SqlFieldVo.StatType.Min.getName().equals(conditiontype) ||
                type == SqlFieldVo.StatType.Min) {
            typeStr = Labels.getLabel("sqlwizard.field.min");
        } else if (SqlFieldVo.StatType.NONE.getName().equals(conditiontype) ||
                type == SqlFieldVo.StatType.NONE) {
            typeStr = "";
        }
        return typeStr;
    }
    private String getSqlStringWithSqlWizardVo() {
        StringBuffer buffer = new StringBuffer();
        if (Strings.isBlank(sqlWizardVo.getSqlFieldVoList().get(0).getFieldName())) {
            //选择字段
            WebUtils.showError(Labels.getLabel("sqlwizard.error.noChooseField"));
        } else if (sqlWizardVo.getSqlTableBeanList().size() == 0) {
            //选择表
            WebUtils.showError(Labels.getLabel("sqlwizard.error.noChooseTable"));
        } else {
            buffer.append("SELECT ");
            for (int fieldIndex = 0; fieldIndex < sqlWizardVo.getSqlFieldVoList().size(); fieldIndex++) {
                SqlFieldVo fieldVo = sqlWizardVo.getSqlFieldVoList().get(fieldIndex);
                if (Strings.isBlank(fieldVo.getFieldName()) == false) {
                    if (fieldVo.getStatType().equals(SqlFieldVo.StatType.NONE.getName())) {
                        buffer.append(sqlWizardVo.getSqlFieldVoList().get(fieldIndex).getFieldName()).append(",");
                    } else {
                        if (fieldVo.getStatType().equals(SqlFieldVo.StatType.AVG.getName()))
                            buffer.append("AVG(");
                        else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.SUM.getName()))
                            buffer.append("SUM(");
                        else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.COUNT.getName()))
                            buffer.append("COUNT(");
                        else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.MAX.getName()))
                            buffer.append("MAX(");
                        else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.Min.getName()))
                            buffer.append("MIN(");
                        buffer.append(sqlWizardVo.getSqlFieldVoList().get(fieldIndex).getFieldName()).append("),");
                    }
                }
            }
            buffer.delete(buffer.length() - 1, buffer.length());

            buffer.append(" FROM ");
            for (int tableIndex = 0; tableIndex < sqlWizardVo.getSqlTableBeanList().size(); tableIndex++) {
                if (Strings.isBlank(sqlWizardVo.getSqlTableBeanList().get(tableIndex).getId()) == false) {
                    buffer.append(sqlWizardVo.getSqlTableBeanList().get(tableIndex).getId()).append(",");
                }
            }
            buffer.delete(buffer.length() - 1, buffer.length());

            Boolean isContainWhere = false;
            if (sqlWizardVo.getSqlConditionVoList().size() > 0) {
                for (int conditionIndex = 0; conditionIndex < sqlWizardVo.getSqlConditionVoList().size(); conditionIndex++) {
                    if (Strings.isBlank(sqlWizardVo.getSqlConditionVoList().get(conditionIndex).getKeyContent()) == false) {
                        isContainWhere = true;
                        break;
                    }
                }
            }
            if (isContainWhere == true) {
                buffer.append(" WHERE ");
                for (int conditionIndex = 0; conditionIndex < sqlWizardVo.getSqlConditionVoList().size(); conditionIndex++) {
                    if (Strings.isBlank(sqlWizardVo.getSqlConditionVoList().get(conditionIndex).getKeyContent()) == false) {
                        SqlConditionVo vo = sqlWizardVo.getSqlConditionVoList().get(conditionIndex);
                        buffer.append(getConditionWithVo(vo));
                    }
                }
                if (buffer.toString().endsWith("AND ")) {
                    buffer.delete(buffer.length() - 5, buffer.length());
                } else {
                    buffer.delete(buffer.length() - 4, buffer.length());
                }
            }

            Boolean isContainGroupBy = false;
            for (int fieldIndex = 0; fieldIndex < sqlWizardVo.getSqlFieldVoList().size(); fieldIndex++) {
                if (Strings.isBlank(sqlWizardVo.getSqlFieldVoList().get(fieldIndex).getFieldName()) == false &&
                        sqlWizardVo.getSqlFieldVoList().get(fieldIndex).getGroup() == true) {
                    isContainGroupBy = true;
                    break;
                }
            }
            if (isContainGroupBy == true) {
                buffer.append(" GROUP BY ");
                for (int fieldIndex = 0; fieldIndex < sqlWizardVo.getSqlFieldVoList().size(); fieldIndex++) {
                    if (Strings.isBlank(sqlWizardVo.getSqlFieldVoList().get(fieldIndex).getFieldName()) == false &&
                            sqlWizardVo.getSqlFieldVoList().get(fieldIndex).getGroup() == true) {
                        buffer.append(sqlWizardVo.getSqlFieldVoList().get(fieldIndex).getFieldName()).append(",");
                    }
                }
                buffer.delete(buffer.length() - 1, buffer.length());
            }

            Boolean isContainHaving = false;
            for (int fieldIndex = 0; fieldIndex < sqlWizardVo.getSqlHavingVoList().size(); fieldIndex++) {
                if (Strings.isBlank(sqlWizardVo.getSqlHavingVoList().get(fieldIndex).getKeyContent()) == false) {
                    isContainHaving = true;
                    break;
                }
            }
            if (isContainHaving == true) {
                buffer.append(" HAVING ");
                for (int conditionIndex = 0; conditionIndex < sqlWizardVo.getSqlHavingVoList().size(); conditionIndex++) {
                    if (Strings.isBlank(sqlWizardVo.getSqlHavingVoList().get(conditionIndex).getKeyContent()) == false) {
                        SqlConditionVo vo = sqlWizardVo.getSqlHavingVoList().get(conditionIndex);
                        buffer.append(getConditionWithVo(vo));
                    }
                }
                if (buffer.toString().endsWith("AND ")) {
                    buffer.delete(buffer.length() - 5, buffer.length());
                } else {
                    buffer.delete(buffer.length() - 4, buffer.length());
                }
            }

            Boolean isContainOrder = false;
            for (int fieldIndex = 0; fieldIndex < sqlWizardVo.getSqlOrderVoList().size(); fieldIndex++) {
                if (Strings.isBlank(sqlWizardVo.getSqlOrderVoList().get(fieldIndex).getKeyContent()) == false) {
                    isContainOrder = true;
                    break;
                }
            }
            if (isContainOrder == true) {
                buffer.append(" ORDER BY ");
                for (int conditionIndex = 0; conditionIndex < sqlWizardVo.getSqlOrderVoList().size(); conditionIndex++) {
                    if (Strings.isBlank(sqlWizardVo.getSqlOrderVoList().get(conditionIndex).getKeyContent()) == false) {
                        SqlOrderVo vo = sqlWizardVo.getSqlOrderVoList().get(conditionIndex);
                        buffer.append(getOrderWithVo(vo));
                    }
                }
                buffer.delete(buffer.length() - 1, buffer.length());
            }

            Boolean isContainLimit = false;
            if (sqlWizardVo.getLimitVo().getLimitLength() > 0) {
                isContainLimit = true;
            }
            if (isContainLimit == true) {
                buffer.append(" LIMIT ");
                buffer.append(sqlWizardVo.getLimitVo().getStartIndex()).append(",").
                        append(sqlWizardVo.getLimitVo().getLimitLength());
            }
        }
        return buffer.toString();
    }

    private String getConditionWithVo(SqlConditionVo vo) {
        StringBuffer buffer = new StringBuffer();
        buffer.append("(");
        if (vo.getConditionType().equals(SqlConditionVo.ConditionType.EQUAL.getName())) {
            buffer.append(vo.getKeyContent()).append(" = '").append(vo.getValueContent()).append("') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.NOEQUAL.getName())) {
            buffer.append(vo.getKeyContent()).append(" <> '").append(vo.getValueContent()).append("') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.LESS.getName())) {
            buffer.append(vo.getKeyContent()).append(" < '").append(vo.getValueContent()).append("') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.LESSOREQUAL.getName())) {
            buffer.append(vo.getKeyContent()).append(" <= '").append(vo.getValueContent()).append("') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.MORE.getName())) {
            buffer.append(vo.getKeyContent()).append(" > '").append(vo.getValueContent()).append("') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.MOREOREQUAL.getName())) {
            buffer.append(vo.getKeyContent()).append(" >= '").append(vo.getValueContent()).append("') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.CONTAIN.getName())) {
            buffer.append(vo.getKeyContent()).append(" LIKE '%").append(vo.getValueContent()).append("%') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.NOCONTAIN.getName())) {
            buffer.append(vo.getKeyContent()).append(" NOT LIKE '%").append(vo.getValueContent()).append("%') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.START.getName())) {
            buffer.append(vo.getKeyContent()).append(" LIKE '").append(vo.getValueContent()).append("%') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.NOSTART.getName())) {
            buffer.append(vo.getKeyContent()).append(" NOT LIKE '").append(vo.getValueContent()).append("%') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.END.getName())) {
            buffer.append(vo.getKeyContent()).append(" LIKE '%").append(vo.getValueContent()).append("') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.NULL.getName())) {
            buffer.append(vo.getKeyContent()).append(" IS NULL) ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.NONULL.getName())) {
            buffer.append(vo.getKeyContent()).append(" IS NOT NULL) ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.EMPTY.getName())) {
            buffer.append(vo.getKeyContent()).append(" = '') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.NOEMPTY.getName())) {
            buffer.append(vo.getKeyContent()).append(" <> '') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.BETWEEN.getName())) {
            buffer.append(vo.getKeyContent()).append(" BETWEEN '").append(vo.getValueContent()).
                    append("' AND '").append(vo.getValueSecondContent()).append("') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.NOBETWEEN.getName())) {
            buffer.append(vo.getKeyContent()).append(" NOT BETWEEN '").append(vo.getValueContent()).
                    append("' AND '").append(vo.getValueSecondContent()).append("') ");
        } else if (vo.getConditionType().equals(SqlConditionVo.ConditionType.IN.getName())) {
            buffer.append(vo.getKeyContent()).append(" IN (").append(vo.getValueContent()).
                    append(vo.getValueSecondContent()).append(")) ");
        } else if (
                vo.getConditionType().equals(SqlConditionVo.ConditionType.NOIN.getName())) {
            buffer.append(vo.getKeyContent()).append(" NO IN (").append(vo.getValueContent()).
                    append(vo.getValueSecondContent()).append(")) ");
        }

        if (vo.getLinkType().equals(SqlConditionVo.LinkType.AND.getName())) {
            buffer.append("AND ");
        } else {
            buffer.append("OR ");
        }
        return buffer.toString();
    }

    private String getOrderWithVo(SqlOrderVo vo) {
        StringBuffer buffer = new StringBuffer();
        buffer.append(vo.getKeyContent());
        if (vo.getOrderType().equals(SqlOrderVo.OrderType.DESC.getName()))
            buffer.append(" DESC");
        else
            buffer.append(" ASC");
        buffer.append(",");
        return buffer.toString();
    }

    //UI
    /**
     * 创建数据表表格
     */
    private void createTableGrid() {
        tableGrid.getRows().getChildren().clear();
        int size = sqlWizardVo.getSqlTableBeanList().size();
        Row row = null;
        if (sqlWizardVo.getSqlTableBeanList().size() % 4 != 0)
            size += (4 - sqlWizardVo.getSqlTableBeanList().size() % 4);
        for (int index = 0; index < size; index++) {
            if (index % 4 == 0) {
                row = new Row();
                row.setStyle("background:#e8e8e8");
                row.setHeight("60px");
            } else if (index % 4 == 3) {
                if (row != null)
                    tableGrid.getRows().appendChild(row);
            }

            if (sqlWizardVo.getSqlTableBeanList().size() - 1 >= index) {
                TableBean vo = sqlWizardVo.getSqlTableBeanList().get(index);
                Window window = new Window();
                window.setHeight("176px");
                window.setTitle(vo.getName());
                window.setClosable(true);
                window.setPage(row.getPage());
                window.addEventListener(Events.ON_CLOSE, this);
                window.setId(vo.getId());
                Listbox listbox = new Listbox();
                listbox.setVflex("1");
                listbox.setHflex("1");
                Listhead listhead = new Listhead();
                listhead.setSizable(true);
                listhead.setParent(listbox);
                Listheader listheader = new Listheader();
                listheader.setLabel(Labels.getLabel("sqlwizard.field"));
                listheader.setHflex("1");
                listheader.setParent(listhead);
                listheader = new Listheader();
                listheader.setLabel(Labels.getLabel("entity.type"));
                listheader.setHflex("1");
                listheader.setParent(listhead);
                listbox.setParent(window);

                for (TableField tableField: vo.getFields()) {
                    Listitem listitem = new Listitem();
                    Listcell nameListcell = new Listcell(tableField.getName());
                    listitem.appendChild(nameListcell);
                    Listcell typeListcell = new Listcell(DaoUtils.getTypeName(tableField.getType()));
                    listitem.appendChild(typeListcell);
                    listitem.setParent(listbox);
                }
                row.appendChild(window);
            } else
                row.appendChild(new Div());
        }
    }

    /**
     * 创建条件表格
     */
    private void createConditionGrid() {
        conditionGrid.getRows().getChildren().clear();
        //字段
        Group fieldGroup = new Group(Labels.getLabel("sqlwizard.field"));
        fieldGroup.appendChild(new Div());
        fieldGroup.appendChild(new Div());
        fieldGroup.appendChild(new Div());
        fieldGroup.appendChild(new Div());
        conditionGrid.getRows().appendChild(fieldGroup);
        if (sqlWizardVo.getSqlFieldVoList().size() == 0) {
            SqlFieldVo fieldVo = new SqlFieldVo();
            fieldVo.setGroup(false);
            fieldVo.setStatType(getSringWithStatType(null, SqlFieldVo.StatType.NONE));
            sqlWizardVo.getSqlFieldVoList().add(fieldVo);
            Row row = createFieldRow(fieldVo);
            conditionGrid.getRows().appendChild(row);
        } else {
            for (SqlFieldVo fieldVo :
                    sqlWizardVo.getSqlFieldVoList()) {
                Row row = createFieldRow(fieldVo);
                conditionGrid.getRows().appendChild(row);
            }
        }

        //逻辑
        Group whereGroup = new Group(Labels.getLabel("sqlwizard.where"));
        whereGroup.appendChild(new Div());
        whereGroup.appendChild(new Div());
        whereGroup.appendChild(new Div());
        whereGroup.appendChild(new Div());
        conditionGrid.getRows().appendChild(whereGroup);
        if (sqlWizardVo.getSqlConditionVoList().size() == 0) {
            SqlConditionVo conditionVo = new SqlConditionVo();
            conditionVo.setConditionType(SqlConditionVo.ConditionType.EQUAL.toString());
            conditionVo.setLinkType(SqlConditionVo.LinkType.AND.toString());
            sqlWizardVo.getSqlConditionVoList().add(conditionVo);
            Row row = createConditionRow(conditionVo, false);
            conditionGrid.getRows().appendChild(row);
        } else {
            for (SqlConditionVo conditionVo :
                    sqlWizardVo.getSqlConditionVoList()) {
                Row row = createConditionRow(conditionVo, false);
                conditionGrid.getRows().appendChild(row);
            }
        }

        //获取所有的字段判断这个字段是否存在
        List<String> fieldNameList = new ArrayList<>();
        for (SqlFieldVo fieldVo : sqlWizardVo.getSqlFieldVoList()) {
            if (fieldVo.getStatType().equals(SqlFieldVo.StatType.NONE.getName()))
                fieldNameList.add(fieldVo.getFieldName());
            else {
                StringBuffer buffer = new StringBuffer();
                if (fieldVo.getStatType().equals(SqlFieldVo.StatType.AVG.getName()))
                    buffer.append("AVG(");
                else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.SUM.getName()))
                    buffer.append("SUM(");
                else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.COUNT.getName()))
                    buffer.append("COUNT(");
                else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.MAX.getName()))
                    buffer.append("MAX(");
                else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.Min.getName()))
                    buffer.append("MIN(");
                buffer.append(fieldVo.getFieldName()).append(")");
                fieldNameList.add(buffer.toString());
            }
        }
        //分组后逻辑
        Group havingGroup = new Group(Labels.getLabel("sqlwizard.having"));
        havingGroup.appendChild(new Div());
        havingGroup.appendChild(new Div());
        havingGroup.appendChild(new Div());
        havingGroup.appendChild(new Div());
        conditionGrid.getRows().appendChild(havingGroup);
        //当数据数据为空
        if (sqlWizardVo.getSqlHavingVoList().size() == 0) {
            SqlConditionVo conditionVo = new SqlConditionVo();
            conditionVo.setConditionType(SqlConditionVo.ConditionType.EQUAL.toString());
            conditionVo.setLinkType(SqlConditionVo.LinkType.AND.toString());
            sqlWizardVo.getSqlHavingVoList().add(conditionVo);
            Row row = createConditionRow(conditionVo, true);
            conditionGrid.getRows().appendChild(row);
        } else {
            for (SqlConditionVo conditionVo :
                    sqlWizardVo.getSqlHavingVoList()) {
                if (fieldNameList.contains(conditionVo.getKeyContent()) == false &&
                        Strings.isBlank(conditionVo.getConditionType())  &&
                        Strings.isBlank(conditionVo.getLinkType()) &&
                        Strings.isBlank(conditionVo.getValueContent()) &&
                        Strings.isBlank(conditionVo.getValueSecondContent())) {
                    conditionVo.setKeyContent("");
                    conditionVo.setConditionType(SqlConditionVo.ConditionType.EQUAL.toString());
                    conditionVo.setLinkType(SqlConditionVo.LinkType.AND.toString());
                    conditionVo.setValueContent("");
                    conditionVo.setValueSecondContent("");
                }
                Row row = createConditionRow(conditionVo, true);
                conditionGrid.getRows().appendChild(row);
            }
        }

        //排序
        Group orderGroup = new Group(Labels.getLabel("sqlwizard.order"));
        orderGroup.appendChild(new Div());
        orderGroup.appendChild(new Div());
        orderGroup.appendChild(new Div());
        orderGroup.appendChild(new Div());
        conditionGrid.getRows().appendChild(orderGroup);
        if (sqlWizardVo.getSqlOrderVoList().size() == 0) {
            SqlOrderVo orderVo = new SqlOrderVo();
            orderVo.setOrderType(SqlOrderVo.OrderType.ASC.getName());
            sqlWizardVo.getSqlOrderVoList().add(orderVo);
            Row row = createOrderRow(orderVo);
            conditionGrid.getRows().appendChild(row);
        } else {
            for (SqlOrderVo orderVo :
                    sqlWizardVo.getSqlOrderVoList()) {
                if (fieldNameList.contains(orderVo.getKeyContent()) == false) {
                    orderVo.setKeyContent("");
                    orderVo.setOrderType(SqlOrderVo.OrderType.DESC.getName());
                }
                Row row = createOrderRow(orderVo);
                conditionGrid.getRows().appendChild(row);
            }
        }

        //数量
        Group limitGroup = new Group(Labels.getLabel("sqlwizard.limit"));
        limitGroup.appendChild(new Div());
        limitGroup.appendChild(new Div());
        limitGroup.appendChild(new Div());
        limitGroup.appendChild(new Div());
        conditionGrid.getRows().appendChild(limitGroup);

        Row row = createLimitRow(sqlWizardVo.getLimitVo());
        conditionGrid.getRows().appendChild(row);

        //按钮
        Row buttonRow = new Row();
        buttonRow.appendChild(new Div());
        buttonRow.appendChild(new Div());
        buttonRow.appendChild(new Div());
        Hlayout saveHlayout = new Hlayout();
        saveHlayout.setHflex("1");
        Div saveDiv = new Div();
        saveDiv.setHflex("1");
        saveHlayout.appendChild(saveDiv);
        Button saveBtn = new Button(Labels.getLabel("button.save"));
        saveBtn.setId("sqlWizardEditor_grid_select_condition_save");
        saveBtn.addEventListener(Events.ON_CLICK, this);
        //saveHlayout.appendChild(saveBtn);
        buttonRow.appendChild(saveHlayout);

        Hlayout hlayout = new Hlayout();
        hlayout.setHflex("1");
        Button clearBtn = new Button(Labels.getLabel("entity.api.clear"));
        clearBtn.setId("sqlWizardEditor_grid_select_condition_clear");
        clearBtn.addEventListener(Events.ON_CLICK, this);
        hlayout.appendChild(clearBtn);
        Button executeBtn = new Button(Labels.getLabel("sqlwizard.execute"));
        executeBtn.setId("sqlWizardEditor_grid_select_condition_execute");
        executeBtn.addEventListener(Events.ON_CLICK, this);
        hlayout.appendChild(executeBtn);
        buttonRow.appendChild(hlayout);
        conditionGrid.getRows().appendChild(buttonRow);
    }

    /**
     * 创建字段行
     * @param fieldVo
     * @return
     */
    private Row createFieldRow(SqlFieldVo fieldVo) {
        Row row = new Row();
        row.setValue(fieldVo);
        Bandbox bandbox = new Bandbox();
        bandbox.setHflex("1");
        //Combobox combobox = createFieldCombobox();
        //combobox.setHflex("1");
        //combobox.setValue(fieldVo.getFieldName());
        bandbox.setValue(fieldVo.getFieldName());
        row.appendChild(bandbox);
        Textbox textbox = new Textbox();
        textbox.setHflex("1");
        textbox.setPlaceholder(Labels.getLabel("sqlwizard.alias"));
        textbox.setValue(fieldVo.getAlias());
        row.appendChild(textbox);
        Checkbox checkbox = new Checkbox(Labels.getLabel("sqlwizard.group"));
        checkbox.setChecked(fieldVo.getGroup());
        row.appendChild(checkbox);
        Combobox statCombobox = createStatCombobox();
        statCombobox.setHflex("1");
        statCombobox.setValue(getSringWithStatType(fieldVo.getStatType(), null));
        row.appendChild(statCombobox);
        Hlayout hlayout = new Hlayout();
        hlayout.setHflex("1");
        Button addButton = new Button();
        addButton.setWidth("35px");
        addButton.setIconSclass("z-icon-plus");
        hlayout.appendChild(addButton);
        Button deleteButton = new Button();
        deleteButton.setWidth("35px");
        deleteButton.setIconSclass("z-icon-minus");
        hlayout.appendChild(deleteButton);
        row.appendChild(hlayout);
        new FieldCell(bandbox, textbox, checkbox, statCombobox,addButton, deleteButton);
        return row;
    }

    private Combobox createFieldCombobox() {
        Combobox combobox = new Combobox();
        for (TableBean table : sqlWizardVo.getSqlTableBeanList()) {
            for (TableField tableField : table.getFields()) {
                Comboitem comboitem = new Comboitem(table.getId() + "." +  tableField.getName());
                comboitem.setValue(table.getId() + "." +  tableField.getName());
                combobox.appendChild(comboitem);
            }
        }
        return combobox;
    }

    private Combobox createStatCombobox() {
        Combobox combobox = new Combobox();
        combobox.setReadonly(true);
        Comboitem emptyComboitem = new Comboitem("");
        emptyComboitem.setValue(SqlFieldVo.StatType.NONE);
        combobox.appendChild(emptyComboitem);
        SqlFieldVo.StatType[] statList = new SqlFieldVo.StatType[]{SqlFieldVo.StatType.COUNT, SqlFieldVo.StatType.SUM,
                SqlFieldVo.StatType.AVG, SqlFieldVo.StatType.MAX, SqlFieldVo.StatType.Min};

        for (SqlFieldVo.StatType statType : statList) {
            Comboitem comboitem = new Comboitem(getSringWithStatType(null, statType));
            comboitem.setValue(statType);
            combobox.appendChild(comboitem);
        }
        return combobox;
    }
    private class FieldCell implements EventListener<Event> {

        private Bandbox fieldNameBandbox;
        private Textbox aliasTextbox;
        private Checkbox groupCheckbox;
        private Combobox statCombobox;
        private Button addButton;
        private Button deleteButton;

        public FieldCell(Bandbox fieldNameBandbox, Textbox aliasTextbox, Checkbox groupCheckbox,
                         Combobox statCombobox, Button addButton, Button deleteButton) {
            this.fieldNameBandbox = fieldNameBandbox;
            this.fieldNameBandbox.addEventListener(Events.ON_OK, this);
            this.fieldNameBandbox.addEventListener(Events.ON_CHANGE, this);
            this.fieldNameBandbox.addEventListener(Events.ON_OPEN, this);
            this.aliasTextbox = aliasTextbox;
            this.aliasTextbox.addEventListener(Events.ON_CHANGE, this);
            this.groupCheckbox = groupCheckbox;
            this.groupCheckbox.addEventListener(Events.ON_CHECK, this);
            this.statCombobox = statCombobox;
            this.statCombobox.addEventListener(Events.ON_CHANGE, this);
            this.addButton = addButton;
            this.addButton.addEventListener(Events.ON_CLICK, this);
            this.deleteButton = deleteButton;
            this.deleteButton.addEventListener(Events.ON_CLICK, this);
        }

        @Override
        public void onEvent(Event event) throws Exception {
            Row row;
            if (event.getTarget() instanceof Button)
                row = (Row) event.getTarget().getParent().getParent();
            else
                row = (Row) event.getTarget().getParent();
            final SqlFieldVo vo = row.getValue();
            if (event.getTarget().equals(aliasTextbox)) {
                vo.setAlias(aliasTextbox.getValue());
            } else if (event.getTarget().equals(groupCheckbox)) {
                vo.setGroup(groupCheckbox.isChecked());
            } else if (event.getTarget().equals(statCombobox)) {
                if (statCombobox.getSelectedItem() != null)
                    vo.setStatType(statCombobox.getSelectedItem().getValue().toString());
                createConditionGrid();
            } else if (event.getTarget().equals(addButton)) {
                SqlFieldVo fieldVo = new SqlFieldVo();
                fieldVo.setGroup(false);
                fieldVo.setStatType(getSringWithStatType(null, SqlFieldVo.StatType.NONE));
                sqlWizardVo.getSqlFieldVoList().add(row.getIndex(), fieldVo);
                createConditionGrid();
            } else if (event.getTarget().equals(deleteButton)) {
                if (sqlWizardVo.getSqlFieldVoList().size() > 1) {
                    sqlWizardVo.getSqlFieldVoList().remove(row.getIndex()-1);
                    createConditionGrid();
                }
            } else if (event.getTarget().equals(fieldNameBandbox)) {
                if (event instanceof OpenEvent) {
                    AbstractView.createSqlFieldView(new EditorCallback<String>() {
                        @Override
                        public String getValue() {
                            return null;
                        }

                        @Override
                        public void setValue(String value) {
                            fieldNameBandbox.setValue(value);
                            vo.setFieldName(value.trim());
                            createConditionGrid();
                        }

                        @Override
                        public String getTitle() {
                            return Labels.getLabel("guide.step.element");
                        }

                        @Override
                        public Page getPage() {
                            return fieldNameBandbox.getPage();
                        }

                        @Override
                        public Editor getEditor() {
                            return null;
                        }
                    }, sqlWizardVo.getSqlTableBeanList()).doOverlapped();
                }
                else {
                    String val;
                    val = ((Bandbox) event.getTarget()).getValue();
                    vo.setFieldName(val.trim());
                    createConditionGrid();
                }

            }
        }
    }

    /**
     * 创建逻辑行
     * @param conditionVo
     * @return
     */
    private Row createConditionRow(SqlConditionVo conditionVo, Boolean isHaving) {
        Row row = new Row();
        row.setValue(conditionVo);
        Combobox combobox;
        if (isHaving == true)
            combobox = createHavingCombobox();
        else
            combobox = createFieldCombobox();
        combobox.setValue(conditionVo.getKeyContent());
        combobox.setHflex("1");
        row.appendChild(combobox);
        Bandbox bandbox = createConditionBandbox(isHaving);
        bandbox.setHflex("1");
        bandbox.setReadonly(true);
        bandbox.setValue(getSringWithConditiontype(conditionVo.getConditionType(), null));
        row.appendChild(bandbox);
        //操作栏
        Hlayout hlayout = new Hlayout();
        hlayout.setHflex("1");
        Combobox linkCombobox = createLinkCombobox();
        hlayout.appendChild(linkCombobox);
        String content = conditionVo.getLinkType().equals(SqlConditionVo.LinkType.AND.getName())  ?
                Labels.getLabel("sqlwizard.condition.and") : Labels.getLabel("sqlwizard.condition.or");
        linkCombobox.setValue(content);
        linkCombobox.setHflex("1");
        linkCombobox.setReadonly(true);
        Button addButton = new Button();
        addButton.setIconSclass("z-icon-plus");
        addButton.setWidth("35px");
        hlayout.appendChild(addButton);
        Button deleteButton = new Button();
        deleteButton.setIconSclass("z-icon-minus");
        deleteButton.setWidth("35px");
        hlayout.appendChild(deleteButton);
        //条件栏
        if (conditionVo.getConditionType().equals(SqlConditionVo.ConditionType.BETWEEN.getName()) ||
                conditionVo.getConditionType().equals(SqlConditionVo.ConditionType.NOBETWEEN.getName())) {
            Textbox valueTextbox = new Textbox();
            valueTextbox.setValue(conditionVo.getValueContent());
            valueTextbox.setHflex("1");
            row.appendChild(valueTextbox);
            Textbox valueSeocndTextbox = new Textbox();
            valueSeocndTextbox.setHflex("1");
            valueSeocndTextbox.setValue(conditionVo.getValueSecondContent());
            row.appendChild(valueSeocndTextbox);
            new WhereCell(combobox, bandbox, valueTextbox, valueSeocndTextbox ,linkCombobox, addButton, deleteButton, isHaving);
        } else if (conditionVo.getConditionType().equals(SqlConditionVo.ConditionType.NULL.getName()) ||
                conditionVo.getConditionType().equals(SqlConditionVo.ConditionType.NONULL.getName()) ||
                conditionVo.getConditionType().equals(SqlConditionVo.ConditionType.EMPTY.getName()) ||
                conditionVo.getConditionType().equals(SqlConditionVo.ConditionType.NOEMPTY.getName())) {
            row.appendChild(new Div());
            row.appendChild(new Div());
            new WhereCell(combobox, bandbox,linkCombobox, addButton, deleteButton, isHaving);
        } else if (conditionVo.getConditionType().equals(SqlConditionVo.ConditionType.IN.getName()) ||
                conditionVo.getConditionType().equals(SqlConditionVo.ConditionType.NOIN.getName())) {
            Hlayout valueLayout = new Hlayout();
            valueLayout.setHflex("1");
            Textbox valueTextbox = new Textbox();
            valueTextbox.setHflex("1");
            valueTextbox.setValue(conditionVo.getValueContent());
            Popup popup = new Popup();
            Label popupLabel = new Label(Labels.getLabel("sqlwizard.condition.in.placeholder"));
            popup.setWidth("300px");
            popup.appendChild(popupLabel);
            Image image = new Image("~./images/help.png");
            image.setTooltip(popup);
            image.setStyle("cursor: help");
            image.setWidth("16px");
            valueLayout.appendChild(valueTextbox);
            valueLayout.appendChild(image);
            valueLayout.appendChild(popup);
            row.appendChild(valueLayout);
            row.appendChild(new Div());
            new WhereCell(combobox, bandbox, valueTextbox,linkCombobox, addButton, deleteButton, isHaving);
        } else {
            Textbox valueTextbox = new Textbox();
            valueTextbox.setValue(conditionVo.getValueContent());
            valueTextbox.setHflex("1");
            row.appendChild(valueTextbox);
            row.appendChild(new Div());
            new WhereCell(combobox, bandbox, valueTextbox,linkCombobox, addButton, deleteButton, isHaving);
        }

        row.appendChild(hlayout);
        return row;
    }

    private Combobox createHavingCombobox() {
        Combobox combobox = new Combobox();
        for (SqlFieldVo fieldVo : sqlWizardVo.getSqlFieldVoList()) {
            if (Strings.isBlank(fieldVo.getStatType()) == false && Strings.isBlank(fieldVo.getFieldName()) == false) {
                if (fieldVo.getStatType().equals(SqlFieldVo.StatType.NONE.getName()) == false) {
                    Comboitem comboitem = new Comboitem(fieldVo.getFieldName());
                    comboitem.setValue(fieldVo);
                    combobox.appendChild(comboitem);
                }
            }
        }
        return combobox;
    }

    private Bandbox createConditionBandbox(final Boolean isHaving) {
        final Bandbox bandbox = new Bandbox();
        SqlConditionVo.ConditionType[] conditionList = new SqlConditionVo.ConditionType[]{SqlConditionVo.ConditionType.EQUAL,
                SqlConditionVo.ConditionType.NOEQUAL, SqlConditionVo.ConditionType.LESS, SqlConditionVo.ConditionType.LESSOREQUAL,
                SqlConditionVo.ConditionType.MORE, SqlConditionVo.ConditionType.MOREOREQUAL, SqlConditionVo.ConditionType.CONTAIN,
                SqlConditionVo.ConditionType.NOCONTAIN, SqlConditionVo.ConditionType.START, SqlConditionVo.ConditionType.NOSTART,
                SqlConditionVo.ConditionType.END, SqlConditionVo.ConditionType.NOEND, SqlConditionVo.ConditionType.NULL,
                SqlConditionVo.ConditionType.NONULL, SqlConditionVo.ConditionType.EMPTY, SqlConditionVo.ConditionType.NOEMPTY,
                SqlConditionVo.ConditionType.BETWEEN, SqlConditionVo.ConditionType.NOBETWEEN, SqlConditionVo.ConditionType.IN,
                SqlConditionVo.ConditionType.NOIN};
        Bandpopup bandpopup = new Bandpopup();
        bandpopup.setHeight("150px");
        bandpopup.setWidth("100px");
        Listbox listbox = new Listbox();
        for (SqlConditionVo.ConditionType condition : conditionList) {
            Listitem listitem = new Listitem();
            Listcell listcell = new Listcell(getSringWithConditiontype(null, condition));
            listitem.appendChild(listcell);
            listitem.setValue(condition);
            listbox.appendChild(listitem);
            listitem.addEventListener(Events.ON_CLICK, new EventListener<Event>() {
                @Override
                public void onEvent(Event event) throws Exception {
                    Listitem selectedItem = (Listitem) event.getTarget();
                    bandbox.setValue(getSringWithConditiontype(null,
                            (SqlConditionVo.ConditionType) selectedItem.getValue()));
                    bandbox.setOpen(false);
                    Row row = (Row) bandbox.getParent();
                    if (isHaving == false) {
                        Integer index = row.getIndex() - sqlWizardVo.getSqlFieldVoList().size() - 2;
                        SqlConditionVo selectedConditionVo = sqlWizardVo.getSqlConditionVoList().get(index);
                        selectedConditionVo.setConditionType(((SqlConditionVo.ConditionType) selectedItem.getValue()).getName());
                    } else {
                        Integer index = row.getIndex() - sqlWizardVo.getSqlFieldVoList().size() - 3 -
                                sqlWizardVo.getSqlConditionVoList().size();
                        SqlConditionVo selectedConditionVo = sqlWizardVo.getSqlHavingVoList().get(index);
                        selectedConditionVo.setConditionType(((SqlConditionVo.ConditionType) selectedItem.getValue()).getName());
                    }
                    createConditionGrid();
                }
            });
        }
        bandpopup.appendChild(listbox);
        bandbox.appendChild(bandpopup);
        return bandbox;
    }

    private Combobox createLinkCombobox() {
        Combobox combobox = new Combobox();
        SqlConditionVo.LinkType[] linkList = new SqlConditionVo.LinkType[]{SqlConditionVo.LinkType.AND, SqlConditionVo.LinkType.OR};
        for (SqlConditionVo.LinkType condition : linkList) {
            String content = condition == SqlConditionVo.LinkType.AND ?
                            Labels.getLabel("sqlwizard.condition.and") : Labels.getLabel("sqlwizard.condition.or");
            Comboitem comboitem = new Comboitem(content);
            comboitem.setValue(condition);
            combobox.appendChild(comboitem);
        }
        return combobox;
    }

    private class WhereCell implements EventListener<Event> {

        private Combobox keyNameCombobox;
        private Bandbox conditionBandbox;
        private Combobox valueNameCombobox;
        private Textbox valueTextbox;
        private Textbox secondValueTextbox;
        private Combobox linkCombobox;
        private Button addButton;
        private Button deleteButton;
        private Boolean isHaving;

        public WhereCell(Combobox keyNameCombobox, Bandbox conditionBandbox, Combobox linkCombobox,
                         Button addButton, Button deleteButton, Boolean isHaving) {
            this.keyNameCombobox = keyNameCombobox;
            this.keyNameCombobox.addEventListener(Events.ON_CHANGE, this);
            this.conditionBandbox = conditionBandbox;
            this.linkCombobox = linkCombobox;
            this.linkCombobox.addEventListener(Events.ON_CHANGE, this);
            this.addButton = addButton;
            this.addButton.addEventListener(Events.ON_CLICK, this);
            this.deleteButton = deleteButton;
            this.deleteButton.addEventListener(Events.ON_CLICK, this);
            this.isHaving = isHaving;
        }

        public WhereCell(Combobox keyNameCombobox, Bandbox conditionBandbox, Combobox valueNameCombobox,
                         Combobox linkCombobox, Button addButton, Button deleteButton, Boolean isHaving) {
            this.keyNameCombobox = keyNameCombobox;
            this.keyNameCombobox.addEventListener(Events.ON_CHANGE, this);
            this.conditionBandbox = conditionBandbox;
            this.valueNameCombobox = valueNameCombobox;
            this.valueNameCombobox.addEventListener(Events.ON_CHANGE, this);
            this.linkCombobox = linkCombobox;
            this.linkCombobox.addEventListener(Events.ON_CHANGE, this);
            this.addButton = addButton;
            this.addButton.addEventListener(Events.ON_CLICK, this);
            this.deleteButton = deleteButton;
            this.deleteButton.addEventListener(Events.ON_CLICK, this);
            this.isHaving = isHaving;
        }

        public WhereCell(Combobox keyNameCombobox, Bandbox conditionBandbox, Textbox valueTextbox,
                         Combobox linkCombobox, Button addButton, Button deleteButton, Boolean isHaving) {
            this.keyNameCombobox = keyNameCombobox;
            this.keyNameCombobox.addEventListener(Events.ON_CHANGE, this);
            this.conditionBandbox = conditionBandbox;
            this.valueTextbox = valueTextbox;
            this.valueTextbox.addEventListener(Events.ON_CHANGE, this);
            this.linkCombobox = linkCombobox;
            this.linkCombobox.addEventListener(Events.ON_CHANGE, this);
            this.addButton = addButton;
            this.addButton.addEventListener(Events.ON_CLICK, this);
            this.deleteButton = deleteButton;
            this.deleteButton.addEventListener(Events.ON_CLICK, this);
            this.isHaving = isHaving;
        }

        public WhereCell(Combobox keyNameCombobox, Bandbox conditionBandbox, Textbox valueTextbox,
                         Textbox secondValueTextbox, Combobox linkCombobox, Button addButton,
                         Button deleteButton, Boolean isHaving) {
            this.keyNameCombobox = keyNameCombobox;
            this.keyNameCombobox.addEventListener(Events.ON_CHANGE, this);
            this.conditionBandbox = conditionBandbox;
            this.valueTextbox = valueTextbox;
            this.valueTextbox.addEventListener(Events.ON_CHANGE, this);
            this.secondValueTextbox = secondValueTextbox;
            this.secondValueTextbox.addEventListener(Events.ON_CHANGE, this);
            this.linkCombobox = linkCombobox;
            this.linkCombobox.addEventListener(Events.ON_CHANGE, this);
            this.addButton = addButton;
            this.addButton.addEventListener(Events.ON_CLICK, this);
            this.deleteButton = deleteButton;
            this.deleteButton.addEventListener(Events.ON_CLICK, this);
            this.isHaving = isHaving;
        }

        @Override
        public void onEvent(Event event) throws Exception {
            Row row;
            if (event.getTarget() instanceof Button || event.getTarget().equals(linkCombobox))
                row = (Row) event.getTarget().getParent().getParent();
            else
                row = (Row) event.getTarget().getParent();
            SqlConditionVo vo = row.getValue();
            if (event.getTarget().equals(keyNameCombobox)) {
                if (keyNameCombobox.getSelectedItem() != null) {
                    if (isHaving == false) {
                        vo.setKeyContent((String) keyNameCombobox.getSelectedItem().getValue());
                    }
                    else {
                        SqlFieldVo fieldVo = keyNameCombobox.getSelectedItem().getValue();
                        StringBuffer buffer = new StringBuffer();
                        if (fieldVo.getStatType().equals(SqlFieldVo.StatType.AVG.getName()))
                            buffer.append("AVG(");
                        else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.SUM.getName()))
                            buffer.append("SUM(");
                        else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.COUNT.getName()))
                            buffer.append("COUNT(");
                        else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.MAX.getName()))
                            buffer.append("MAX(");
                        else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.Min.getName()))
                            buffer.append("MIN(");
                        buffer.append(fieldVo.getFieldName()).append(")");
                        vo.setKeyContent(buffer.toString());
                    }
                } else
                    vo.setKeyContent((String) keyNameCombobox.getValue());
            } else if (event.getTarget().equals(valueNameCombobox)) {
                if (valueNameCombobox.getSelectedItem() != null)
                    vo.setValueContent((String) valueNameCombobox.getSelectedItem().getValue());
                else
                    vo.setValueContent((String) valueNameCombobox.getValue());
            } else if (event.getTarget().equals(valueTextbox)) {
                vo.setValueContent(valueTextbox.getValue());
            } else if (event.getTarget().equals(secondValueTextbox)) {
                vo.setValueSecondContent(secondValueTextbox.getValue());
            } else if (event.getTarget().equals(linkCombobox)) {
                vo.setLinkType(linkCombobox.getSelectedItem().getValue().toString());
            } else if (event.getTarget().equals(addButton)) {
                SqlConditionVo conditionVo = new SqlConditionVo();
                conditionVo.setConditionType(SqlConditionVo.ConditionType.EQUAL.toString());
                conditionVo.setLinkType(SqlConditionVo.LinkType.AND.toString());
                Integer index = row.getIndex() - sqlWizardVo.getSqlFieldVoList().size() - 1;
                sqlWizardVo.getSqlConditionVoList().add(index, conditionVo);
                createConditionGrid();
            } else if (event.getTarget().equals(deleteButton)) {
                if (sqlWizardVo.getSqlConditionVoList().size() > 1) {
                    int index = row.getIndex() - sqlWizardVo.getSqlFieldVoList().size() - 2;
                    sqlWizardVo.getSqlConditionVoList().remove(index);
                    createConditionGrid();
                }
            }
        }
    }

    private Row createOrderRow(SqlOrderVo orderVo) {
        Row row = new Row();
        row.setValue(orderVo);
        Combobox combobox = createOrderCombobox();
        combobox.setReadonly(true);
        combobox.setHflex("1");
        combobox.setValue(orderVo.getKeyContent());
        row.appendChild(combobox);
        Radiogroup radiogroup = new Radiogroup();
        radiogroup.setHflex("1");
        radiogroup.appendItem(Labels.getLabel("sqlwizard.order.asc"), SqlOrderVo.OrderType.ASC.getName());
        radiogroup.appendItem(Labels.getLabel("sqlwizard.order.desc"), SqlOrderVo.OrderType.DESC.getName());
        if (orderVo.getOrderType().equals(SqlOrderVo.OrderType.ASC.getName()))
            radiogroup.setSelectedIndex(0);
        else
            radiogroup.setSelectedIndex(1);
        row.appendChild(radiogroup);
        row.appendChild(new Div());
        row.appendChild(new Div());
        Hlayout hlayout = new Hlayout();
        hlayout.setHflex("1");
        Button addButton = new Button();
        addButton.setWidth("35px");
        addButton.setIconSclass("z-icon-plus");
        hlayout.appendChild(addButton);
        Button deleteButton = new Button();
        deleteButton.setWidth("35px");
        deleteButton.setIconSclass("z-icon-minus");
        hlayout.appendChild(deleteButton);
        row.appendChild(hlayout);
        new OrderCell(combobox, radiogroup, addButton, deleteButton);
        return row;
    }

    private Combobox createOrderCombobox() {
        Combobox combobox = new Combobox();
        for (SqlFieldVo fieldVo : sqlWizardVo.getSqlFieldVoList()) {
            if (Strings.isBlank(fieldVo.getFieldName()) == false) {
                Boolean isContain = false;
                for (SqlOrderVo orderVo : sqlWizardVo.getSqlOrderVoList()) {
                    if (Strings.isBlank(orderVo.getKeyContent()) == false) {
                        if (orderVo.getKeyContent().equals(fieldVo.getFieldName())) {
                            isContain = true;
                            break;
                        }
                    }
                }
                if (isContain == false) {
                    Comboitem comboitem = new Comboitem(fieldVo.getFieldName());
                    comboitem.setValue(fieldVo);
                    combobox.appendChild(comboitem);
                }
            }

        }
        return combobox;
    }

    private class OrderCell implements EventListener<Event> {

        private Combobox fieldNameCombobox;
        private Radiogroup typeRadiogroup;
        private Button addButton;
        private Button deleteButton;

        public OrderCell(Combobox fieldNameCombobox, Radiogroup typeRadiogroup, Button addButton, Button deleteButton) {
            this.fieldNameCombobox = fieldNameCombobox;
            this.fieldNameCombobox.addEventListener(Events.ON_CHANGE, this);
            this.typeRadiogroup = typeRadiogroup;
            this.typeRadiogroup.addEventListener(Events.ON_CHECK, this);
            this.addButton = addButton;
            this.addButton.addEventListener(Events.ON_CLICK, this);
            this.deleteButton = deleteButton;
            this.deleteButton.addEventListener(Events.ON_CLICK, this);
        }

        @Override
        public void onEvent(Event event) throws Exception {
            Row row;
            if (event.getTarget() instanceof Button)
                row = (Row) event.getTarget().getParent().getParent();
            else
                row = (Row) event.getTarget().getParent();
            SqlOrderVo vo = row.getValue();
            if (event.getTarget().equals(fieldNameCombobox)) {
                SqlFieldVo fieldVo = fieldNameCombobox.getSelectedItem().getValue();
                StringBuffer buffer = new StringBuffer();
                if (fieldVo.getStatType().equals(SqlFieldVo.StatType.AVG.getName()))
                    buffer.append("AVG(");
                else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.SUM.getName()))
                    buffer.append("SUM(");
                else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.COUNT.getName()))
                    buffer.append("COUNT(");
                else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.MAX.getName()))
                    buffer.append("MAX(");
                else if (fieldVo.getStatType().equals(SqlFieldVo.StatType.Min.getName()))
                    buffer.append("MIN(");
                buffer.append(fieldVo.getFieldName()).append(")");
                vo.setKeyContent(buffer.toString());
                createConditionGrid();
            } else if (event.getTarget().equals(typeRadiogroup)) {
                vo.setOrderType((String) typeRadiogroup.getSelectedItem().getValue());
            } else if (event.getTarget().equals(addButton)) {
                SqlOrderVo sqlOrderVo = new SqlOrderVo();
                sqlOrderVo.setOrderType(SqlOrderVo.OrderType.ASC.getName());
                Integer index = row.getIndex() - sqlWizardVo.getSqlFieldVoList().size() -
                        sqlWizardVo.getSqlConditionVoList().size() - 2;
                sqlWizardVo.getSqlOrderVoList().add(index, sqlOrderVo);
                createConditionGrid();
            } else if (event.getTarget().equals(deleteButton)) {
                if (sqlWizardVo.getSqlOrderVoList().size() > 1) {
                    int index = row.getIndex() - sqlWizardVo.getSqlFieldVoList().size() -
                            sqlWizardVo.getSqlConditionVoList().size() - 3;
                    sqlWizardVo.getSqlOrderVoList().remove(index);
                    createConditionGrid();
                }
            }
        }
    }

    private Row createLimitRow(final SqlLimitVo limitVo) {
        Row row = new Row();
        Hlayout startHlayout = new Hlayout();
        startHlayout.setHflex("1");
        Label startLabel = new Label(Labels.getLabel("sqlwizard.limit.start"));
        startHlayout.appendChild(startLabel);
        Intbox startIntBox = new Intbox();
        startIntBox.setHflex("1");
        if (limitVo.getLimitLength() > 0)
        startIntBox.setValue(limitVo.getStartIndex());
        startIntBox.setPlaceholder(Labels.getLabel("sqlwizard.limit.start.placeholder"));
        startIntBox.addEventListener(Events.ON_CHANGE, new EventListener<Event>() {
            @Override
            public void onEvent(Event event) throws Exception {
                limitVo.setStartIndex(((Intbox)event.getTarget()).getValue());
            }
        });
        startHlayout.appendChild(startIntBox);
        row.appendChild(startHlayout);

        Hlayout lengthHlayout = new Hlayout();
        lengthHlayout.setHflex("1");
        Label lengthLabel = new Label(Labels.getLabel("sqlwizard.limit.length"));
        lengthHlayout.appendChild(lengthLabel);
        Intbox lengthIntBox = new Intbox();
        lengthIntBox.setHflex("1");
        if (limitVo.getLimitLength() > 0)
            lengthIntBox.setValue(limitVo.getLimitLength());
        lengthHlayout.appendChild(lengthIntBox);
        lengthIntBox.addEventListener(Events.ON_CHANGE, new EventListener<Event>() {
            @Override
            public void onEvent(Event event) throws Exception {
                limitVo.setLimitLength(((Intbox)event.getTarget()).getValue());
            }
        });
        row.appendChild(lengthHlayout);

        row.appendChild(new Div());
        row.appendChild(new Div());
        row.appendChild(new Div());
        return row;
    }
}
